package besta.moon.command;

import java.sql.*;

import besta.moon.MoonResultSet;
import besta.moon.statement.MoonStatement;
import besta.moon.engine.RowSource;
import besta.moon.engine.selector.DataSource;
import besta.moon.engine.selector.Scrollable;
import besta.moon.engine.selector.multioper.Distinct;
import besta.moon.engine.selector.multioper.Join;
import besta.moon.engine.selector.multioper.Where;
import besta.moon.engine.selector.result.NoFromResult;
import besta.moon.engine.table.Columns;
import besta.moon.sql.Expression;
import besta.moon.sql.expression.ExpressionName;
import besta.moon.MoonException;
import besta.moon.MoonConnection;
import besta.moon.MoonCode;
import besta.moon.MoonLogger;
import besta.moon.MoonCommand;
import besta.moon.engine.View;
import besta.moon.engine.selector.DataSources;
import besta.moon.engine.selector.multioper.GroupResult;
import besta.moon.engine.selector.multioper.SortedResult;
import besta.moon.engine.selector.result.TableViewResult;
import besta.moon.sql.expression.ExpressionValue;
import besta.moon.sql.expression.Expressions;

public class CommandSelect extends MoonCommand {

    private DataSources tables; 
    private Expression where;
    public RowSource from;
    private Expressions groupBy;
    private Expression having;
    private Expressions orderBy;
    private boolean isAggregateFunction;
    private int maxRows = -1;

    private boolean isDistinct;

    public CommandSelect(MoonLogger log) {
        super(log);
    }

    public CommandSelect(MoonLogger log, Expressions columnExpressions) {
        super(log, columnExpressions);
    }

    public boolean compile(MoonConnection con) throws Exception {
        boolean needCompile = false;
        if (tables != null) {
            for (int i = 0; i < tables.size(); i++) {
                DataSource fromEntry = tables.get(i);
                needCompile |= fromEntry.init(con);
            }
        }

        if (from == null) {
            from = new NoFromResult();
            tables = new DataSources();
            needCompile = true;
        }
        if (!needCompile) {
            return false;
        }

        for (int i = 0; i < columnExpressions.size(); i++) {
            Expression col = columnExpressions.get(i);
            if (col.getAlias() == null) {
                col.setAlias("col" + (i + 1));
            }

            if (col.getType() != Expression.NAME) {
                compileLinkExpressionParams(col);
                continue;
            }

            ExpressionName expr = (ExpressionName) col;

            if ("*".equals(expr.getName())) {
                String tableAlias = expr.getTableAlias();
                if (tableAlias != null) {
                    int t = 0;
                    for (; t < tables.size(); t++) {
                        DataSource fromEntry = tables.get(t);
                        if (tableAlias.equalsIgnoreCase(fromEntry.getAlias())) {
                            View table = fromEntry.getTableView();
                            columnExpressions.remove(i);
                            i = compileAdd_All_Table_Columns(fromEntry, table, i) - 1;
                            break;
                        }
                    }
                    if (t == tables.size()) {
                        throw MoonException.create(MoonCode.COL_WRONG_PREFIX, new Object[]{tableAlias});
                    }
                } else {
                    columnExpressions.remove(i);
                    for (int t = 0; t < tables.size(); t++) {
                        DataSource fromEntry = tables.get(t);
                        View table = fromEntry.getTableView();
                        i = compileAdd_All_Table_Columns(fromEntry, table, i);
                    }
                    i--;
                }
            } else {
                compileLinkExpressionName(expr);
            }

        }
        if (where != null) {
            compileLinkExpression(where);
        }
        if (having != null) {
            compileLinkExpression(having);
        }
        if (orderBy != null) {
            for (int i = 0; i < orderBy.size(); i++) {
                compileLinkExpression(orderBy.get(i));
            }
        }
        if (groupBy != null) {
            for (int i = 0; i < groupBy.size(); i++) {
                compileLinkExpression(groupBy.get(i));
            }
        }

        if (from instanceof Join) {
            compileJoin((Join) from);
        }

        if (where != null) {
            from = new Where(from, where);
        }

        if (isGroupResult()) {
            from = new GroupResult(this, from, groupBy, having, orderBy);
            if (having != null) {
                from = new Where(from, having);
            }
        }

        if (isDistinct) {
            from = new Distinct(from, columnExpressions);
        }

        if (orderBy != null) {
            from = new SortedResult(from, orderBy);
        }

        return true;
    }

    public final boolean isGroupResult() {
        return groupBy != null || having != null || isAggregateFunction;
    }

    private void compileJoin(Join singleJoin) throws Exception {
        if (singleJoin.condition != null) {
            compileLinkExpressionParams(singleJoin.condition);
        }
        if (singleJoin.left instanceof Join) {
            compileJoin((Join) singleJoin.left);
        }
        if (singleJoin.right instanceof Join) {
            compileJoin((Join) singleJoin.right);
        }
    }

    private void compileLinkExpression(Expression expr) throws Exception {
        if (expr.getType() == Expression.NAME) {
            compileLinkExpressionName((ExpressionName) expr);
        } else {
            compileLinkExpressionParams(expr);
        }
    }

    private void compileLinkExpressionName(ExpressionName expr) throws Exception {
        String tableAlias = expr.getTableAlias();
        if (tableAlias != null) {
            int t = 0;
            for (; t < tables.size(); t++) {
                DataSource fromEntry = tables.get(t);
                if (tableAlias.equalsIgnoreCase(fromEntry.getAlias())) {
                    View table = fromEntry.getTableView();
                    int colIdx = table.findColumnIdx(expr.getName());
                    if (colIdx >= 0) {
                        expr.setFrom(fromEntry, colIdx, table);
                        break;
                    } else {
                        throw MoonException.create(MoonCode.COL_INVALID_NAME, new Object[]{expr.getName()});
                    }
                }
            }
            if (t == tables.size()) {
                throw MoonException.create(MoonCode.COL_WRONG_PREFIX, tableAlias);
            }
        } else {
            boolean isSetFrom = false;
            for (int t = 0; t < tables.size(); t++) {
                DataSource fromEntry = tables.get(t);
                View table = fromEntry.getTableView();
                int colIdx = table.findColumnIdx(expr.getName());
                if (colIdx >= 0) {
                    if (isSetFrom) {
                        throw MoonException.create(MoonCode.COL_AMBIGUOUS, expr.getName());
                    }
                    isSetFrom = true;
                    expr.setFrom(fromEntry, colIdx, table);
                }
            }
            if (!isSetFrom) {
                throw MoonException.create(MoonCode.COL_INVALID_NAME, expr.getName());
            }
        }
        compileLinkExpressionParams(expr);
    }

    private void compileLinkExpressionParams(Expression expr) throws Exception {
        Expression[] expParams = expr.getParams();
        isAggregateFunction = isAggregateFunction || expr.getType() >= Expression.GROUP_BEGIN;
        if (expParams != null) {
            for (Expression param : expParams) {
                int paramType = param.getType();
                isAggregateFunction = isAggregateFunction || paramType >= Expression.GROUP_BEGIN;
                if (paramType == Expression.NAME) {
                    compileLinkExpressionName((ExpressionName) param);
                } else {
                    compileLinkExpressionParams(param);
                }
            }
        }
        expr.optimize();
    }

    private int compileAdd_All_Table_Columns(DataSource fromEntry, View table, int position) {
        for (int k = 0; k < table.columns.size(); k++) {
            ExpressionName expr = new ExpressionName(table.columns.get(k).getName());
            expr.setFrom(fromEntry, k, table);
            columnExpressions.add(position++, expr);
        }
        return position;
    }

    @Override
    public void executeImpl(MoonConnection con, MoonStatement st) throws Exception {
        compile(con);
        if ((st.rsType == ResultSet.TYPE_SCROLL_INSENSITIVE || st.rsType == ResultSet.TYPE_SCROLL_SENSITIVE)
                && !from.isScrollable()) {
            from = new Scrollable(from);
        }
        from.execute();
        rs = new MoonResultSet(st, this);
    }

    public void beforeFirst() throws Exception {
        from.beforeFirst();
    }

    public boolean isBeforeFirst() throws SQLException {
        return from.isBeforeFirst();
    }

    public boolean isFirst() throws SQLException {
        return from.isFirst();
    }

    public boolean first() throws Exception {
        return from.first();
    }

    public boolean previous() throws Exception {
        return from.previous();
    }

    public boolean next() throws Exception {
        if (maxRows >= 0 && from.getRow() >= maxRows) {
            from.afterLast();
            return false;
        }
        return from.next();
    }

    public final boolean last() throws Exception {
        if (maxRows >= 0) {
            if (maxRows == 0) {
                from.beforeFirst();
                return false;
            }
            return from.absolute(maxRows);
        }
        return from.last();
    }

    public final void afterLast() throws Exception {
        from.afterLast();
    }

    public boolean isLast() throws Exception {
        return from.isLast();
    }

    public boolean isAfterLast() throws Exception {
        return from.isAfterLast();
    }

    public final boolean absolute(int row) throws Exception {
        return from.absolute(row);
    }

    public final boolean relative(int rows) throws Exception {
        return from.relative(rows);
    }

    public final int getRow() throws Exception {
        int row = from.getRow();
        if (maxRows >= 0 && row > maxRows) {
            return 0;
        }
        return row;
    }

    public final void updateRow(MoonConnection con, Expression[] newRowSources) throws SQLException {
        int savepoint = con.getSavepoint();
        try {
            for (int t = 0; t < tables.size(); t++) {
                TableViewResult result = TableViewResult.getTableViewResult(tables.get(t));
                View table = result.getTableView();
                Columns tableColumns = table.columns;
                int count = tableColumns.size();
                Expression[] updateValues = new Expression[count];
                boolean isUpdateNeeded = false;
                for (int i = 0; i < columnExpressions.size(); i++) {
                    Expression src = newRowSources[i];
                    if (src != null && (!(src instanceof ExpressionValue) || !((ExpressionValue) src).isEmpty())) {
                        Expression col = columnExpressions.get(i);
                        if (!col.isDefinitelyWritable()) {
                            throw MoonException.create(MoonCode.COL_READONLY, i);
                        }
                        ExpressionName exp = (ExpressionName) col;
                        if (table == exp.getTable()) {
                            updateValues[exp.getColumnIndex()] = src;
                            isUpdateNeeded = true;
                        }
                    }
                }

                if (isUpdateNeeded) {
                    result.updateRow(updateValues);
                }
            }
        } catch (Exception e) {
            con.rollback(savepoint);
            throw MoonException.createFromException(e);
        } finally {
            if (con.getAutoCommit()) {
                con.commit();
            }
        }
    }

    public final void insertRow(MoonConnection con, Expression[] newRowSources) throws SQLException {
        if (tables.size() > 1) {
            throw MoonException.create(MoonCode.JOIN_INSERT);
        }
        if (tables.size() == 0) {
            throw MoonException.create(MoonCode.INSERT_WO_FROM);
        }

        int savepoint = con.getSavepoint();
        try {
            TableViewResult result = TableViewResult.getTableViewResult(tables.get(0));
            View table = result.getTableView();
            Columns tabColumns = table.columns;
            int count = tabColumns.size();
            Expression[] updateValues = new Expression[count];
            if (newRowSources != null) {
                for (int i = 0; i < columnExpressions.size(); i++) {
                    Expression src = newRowSources[i];
                    if (src != null && (!(src instanceof ExpressionValue) || !((ExpressionValue) src).isEmpty())) {
                        Expression rsColumn = columnExpressions.get(i); 
                        if (!rsColumn.isDefinitelyWritable()) {
                            throw MoonException.create(MoonCode.COL_READONLY, i);
                        }
                        ExpressionName exp = (ExpressionName) rsColumn;
                        if (table == exp.getTable()) {
                            updateValues[exp.getColumnIndex()] = src;
                            continue;
                        }
                    }
                    updateValues[i] = null;
                }
            }

            result.insertRow(updateValues);
        } catch (Exception e) {
            con.rollback(savepoint);
            throw MoonException.createFromException(e);
        } finally {
            if (con.getAutoCommit()) {
                con.commit();
            }
        }
    }

    public final void deleteRow(MoonConnection con) throws SQLException {
        int savepoint = con.getSavepoint();
        try {
            if (tables.size() > 1) {
                throw MoonException.create(MoonCode.JOIN_DELETE);
            }
            if (tables.size() == 0) {
                throw MoonException.create(MoonCode.DELETE_WO_FROM);
            }
            TableViewResult.getTableViewResult(tables.get(0)).deleteRow();
        } catch (SQLException e) {
            con.rollback(savepoint);
            throw MoonException.createFromException(e);
        } finally {
            if (con.getAutoCommit()) {
                con.commit();
            }
        }
    }

    public int findColumn(String columnName) throws SQLException {
        Expressions columns = columnExpressions;
        for (int i = 0; i < columns.size(); i++) {
            if (columnName.equalsIgnoreCase(columns.get(i).getAlias())) {
                return i;
            }
        }
        throw MoonException.create(MoonCode.COL_MISSING, columnName);
    }

    public final void setDistinct(boolean distinct) {
        this.isDistinct = distinct;
    }

    public final void setSource(RowSource join) {
        this.from = join;
    }

    public final void setTables(DataSources from) {
        this.tables = from;
    }

    public final void setWhere(Expression where) {
        this.where = where;
    }

    public final void setGroup(Expressions group) {
        this.groupBy = group;
    }

    public final void setHaving(Expression having) {
        this.having = having;
    }

    public final void setOrder(Expressions order) {
        this.orderBy = order;
    }

    @Override
    public final void setMaxRows(int max) {
        maxRows = max;
    }

    @Override
    public final int getMaxRows() {
        return maxRows;
    }
}
